﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using Reporting;
using BusinessObjects;
using System.Globalization;

namespace DataAccess {
  /// <summary>
  /// Data Access Layer for reports
  /// </summary>
  public class ReportDal {

    #region - Public methods -

    /// <summary>
    /// Gets the childfiche report data.
    /// </summary>
    /// <param name="children">The children.</param>
    /// <returns></returns>
    public ChildficheData GetChildficheReportData( Children children ) {
      ChildficheData data = new ChildficheData( );
      using ( SQLiteConnection connection = DataHelper.CreateConnection( ) ) {
        using ( SQLiteCommand command = CreateChildficheSelectCommand( connection, children ) ) {
          using ( SQLiteDataAdapter adapter = new SQLiteDataAdapter( command ) ) {
            try {
              adapter.TableMappings.Add( "Table", "ChildFiche" );
              adapter.Fill( data );
            } catch ( Exception ) {
              throw;
            } finally {
              if ( connection.State != System.Data.ConnectionState.Closed ) {
                connection.Close( );
              }
            }
          }
        }
      }
      return data;
    }

    /// <summary>
    /// Gets the childfiche detail report data.
    /// </summary>
    /// <param name="children">The children.</param>
    /// <returns></returns>
    public ChildficheDetailData GetChildficheDetailReportData( Children children ) {
      ChildficheDetailData data = new ChildficheDetailData( );
      using ( SQLiteConnection connection = DataHelper.CreateConnection( ) ) {
        using ( SQLiteCommand command = CreateChildrenDetailSelectCommand( connection, children ) ) {
          using ( SQLiteDataAdapter adapter = new SQLiteDataAdapter( command ) ) {
            try {
              adapter.TableMappings.Add( "Table", "Children" );
              adapter.Fill( data );
            } catch ( Exception ) {
              throw;
            } finally {
              if ( connection.State != System.Data.ConnectionState.Closed ) {
                connection.Close( );
              }
            }
          }
        }
      }
      return data;
    }

    /// <summary>
    /// Gets the month invoice.
    /// </summary>
    /// <param name="fromDate">From date.</param>
    /// <param name="toDate">To date.</param>
    /// <returns></returns>
    public InvoiceMonthReportData GetMonthInvoice( DateTime fromDate, DateTime toDate ) {
      MonthInvoiceData data = new MonthInvoiceData( );
      using ( SQLiteConnection connection = DataHelper.CreateConnection( ) ) {
        using ( SQLiteCommand command = CreateMonthInvoiceSelectCommand( connection, fromDate, toDate ) ) {
          using ( SQLiteDataAdapter adapter = new SQLiteDataAdapter( command ) ) {
            try {
              adapter.TableMappings.Add( "Table", "MonthInvoice" );
              adapter.Fill( data );
            } catch ( Exception ) {
              throw;
            } finally {
              if ( connection.State != System.Data.ConnectionState.Closed ) {
                connection.Close( );
              }
            }
          }
        }
      }

      return SetupInvoiceMonthData( data );
    }

    #endregion

    #region - Private methods -

    #region - SQL statements -

    private SQLiteCommand CreateChildficheSelectCommand( SQLiteConnection connection, Children children ) {
      SQLiteCommand command = connection.CreateCommand( );
      command.CommandText = @"SELECT Children.ID AS ChildId,Children.[Name] AS ChildName, Children.FirstName AS ChildFirstName, 
       Children.[NickName] AS ChildNickName, Children.[Birthday] AS ChildBirthday,       
       Children.[DateIn] AS ChildDateIn, Children.[DateOut] AS ChildDateOut,       
       Children.[MaxDaysPerMonth] AS ChildMaxDays, Children.[MinDaysPerMonth] AS ChildMinDays,       
       Children.[MinStartTime] AS ChildMinTime, Children.[MaxStopTime] AS ChildMaxTime,       
       Children.[State] AS ChildState, Children.[ActiveState] AS ChildActiveState,
       Children.[Photo] AS ChildPhoto, Children.ChildLinkId AS ChildLink,
       Children.[Remarks] AS ChildRemarks, 
       Mothers.ID AS MotherId, Mothers.Name AS MotherName, Mothers.[FirstName] AS MotherFirstName,
       Mothers.[Phone] AS MotherPhone, Mothers.[GSM] AS MotherGsm, Mothers.[Email] AS MotherEmail,       
       Mothers.[NationalNumber] AS MotherNationalNumber, Mothers.[Photo] AS MotherPhoto, 
       Mothers.Street AS MotherStreet, Mothers.MainAddress AS MotherMainAddress,       
       mZipCodes.[ID] AS MotherZipcodeId,
       mZipCodes.ZipCode AS MotherZipcode, mZipCodes.[City] AS MotherCity, 
       mCountries.Country AS MotherCountry, mCountries.[CountryCode] AS MotherCountryCode,       
       mCountries.[ID] AS MotherCountryId,
       
       Fathers.ID AS FatherId, Fathers.Name AS FatherName, Fathers.[FirstName] AS FatherFirstName,       
       Fathers.[Phone] AS FatherPhone, Fathers.[GSM] AS FatherGsm, Fathers.[Email] AS FatherEmail,       
       Fathers.[NationalNumber] AS FatherNationalNumber, Fathers.[Photo] AS FatherPhoto,
       Fathers.Street AS FatherStreet, Fathers.MainAddress AS FatherMainAddress,
       fZipCodes.[City] AS FatherCity, fZipCodes.ZipCode AS FatherZipcode,        
       fZipCodes.[ID] AS FatherZipcodeId, fCountries.[CountryCode] AS FatherCountryCode,
       fCountries.Country AS FatherCountry, fCountries.[ID] AS FatherCountryId
           

FROM Children INNER JOIN Mothers ON Children.MotherId = Mothers.ID
     LEFT JOIN Fathers ON Children.FatherId = Fathers.ID
     LEFT JOIN ZipCodes mZipCodes ON Mothers.ZipcodeId = mZipCodes.ID 
     LEFT JOIN ZipCodes fZipCodes ON Fathers.ZipcodeId = fZipCodes.ID 
     LEFT JOIN Countries mCountries ON mZipCodes.CountryId = mCountries.ID 
     LEFT JOIN Countries fCountries ON fZipCodes.CountryId = fCountries.ID ";
      if ( children.Count > 0 ) {
        StringBuilder sb = new StringBuilder( " WHERE Children.ID IN (" );
        foreach ( Child child in children ) {
          sb.Append( child.Id.ToString( CultureInfo.CurrentCulture ) + "," );
        }
        command.CommandText = command.CommandText + sb.ToString( 0, sb.Length - 1 ) + ");";
      }
      return command;
    }

    private SQLiteCommand CreateChildrenDetailSelectCommand( SQLiteConnection connection, Children children ) {
      SQLiteCommand command = connection.CreateCommand( );
      command.CommandText = SingleDetailSelect( );
      if ( children.Count > 0 ) {
        StringBuilder sb = new StringBuilder( " WHERE Children.ID IN (" );
        foreach ( Child child in children ) {
          sb.Append( child.Id.ToString( CultureInfo.CurrentCulture ) + "," );
        }
        command.CommandText = command.CommandText + sb.ToString( 0, sb.Length - 1 ) + ");";
      }

      command.Parameters.AddWithValue( "@date1", DateTime.Now.Date );
      command.Parameters.AddWithValue( "@date2", DateTime.Now.Date.AddDays( 1 ) );
      return command;
    }

    private SQLiteCommand CreateMonthInvoiceSelectCommand( SQLiteConnection connection, DateTime fromDate, DateTime toDate ) {
      SQLiteCommand command = connection.CreateCommand( );
      command.CommandText = @"SELECT Children.ID AS ChildId,
       Children.[Name] AS ChildName, 
       Children.FirstName AS ChildFirstName, 
       Children.[DateIn] AS ChildDateIn, 
       Children.[DateOut] AS ChildDateOut,              
       Children.[MinStartTime] AS ChildMinTime, 
       Children.[MaxStopTime] AS ChildMaxTime,    
      
       Mothers.ID AS MotherId, 
       Mothers.Name AS MotherName, 
       Mothers.[FirstName] AS MotherFirstName,
       Mothers.Street AS MotherStreet, 
       Mothers.MainAddress AS MotherMainAddress, 
       Mothers.BasicPrice AS BasicPrice,          
       mZipCodes.[ID] AS MotherZipcodeId,
       mZipCodes.ZipCode AS MotherZipcode, 
       mZipCodes.[City] AS MotherCity, 
       mCountries.[CountryCode] AS MotherCountryCode,       
       mCountries.[ID] AS MotherCountryId,
        
       Fathers.ID AS FatherId, 
       Fathers.Name AS FatherName, 
       Fathers.[FirstName] AS FatherFirstName,  
       Fathers.Street AS FatherStreet, 
       Fathers.MainAddress AS FatherMainAddress,
       fZipCodes.[City] AS FatherCity, 
       fZipCodes.ZipCode AS FatherZipcode,        
       fZipCodes.[ID] AS FatherZipcodeId, 
       fCountries.[CountryCode] AS FatherCountryCode,
        fCountries.[ID] AS FatherCountryId, 
            
       ChildrenDiaries.[ArrivalDateTime] AS ArrivalDateTime,       
       ChildrenDiaries.[DepartureDateTime] AS DepartureDateTime

FROM Children INNER JOIN Mothers ON Children.MotherId = Mothers.ID 
     LEFT JOIN Schedule ON Children.ID = Schedule.ChildId
     LEFT JOIN Fathers ON Children.FatherId = Fathers.ID 
     LEFT JOIN ZipCodes mZipCodes ON Mothers.ZipcodeId = mZipCodes.ID 
     LEFT JOIN ZipCodes fZipCodes ON Fathers.ZipcodeId = fZipCodes.ID 
     LEFT JOIN Countries mCountries ON mZipCodes.CountryId = mCountries.ID 
     LEFT JOIN Countries fCountries ON fZipCodes.CountryId = fCountries.ID      
     LEFT OUTER JOIN ChildrenDiaries on Children.ID = ChildrenDiaries.ChildId AND ChildrenDiaries.[ArrivalDateTime] between @date1 and @date2";
      command.Parameters.AddWithValue( "@date1", fromDate );
      command.Parameters.AddWithValue( "@date2", toDate );
      return command;
    }

    #endregion

    private string SingleDetailSelect( ) {
      return @"SELECT Children.ID AS ChildId,Children.[Name] AS ChildName, 
       Children.FirstName AS ChildFirstName, 
       Children.[NickName] AS ChildNickName, Children.[Birthday] AS ChildBirthday,       
       Children.[DateIn] AS ChildDateIn, Children.[DateOut] AS ChildDateOut,       
       Children.[MaxDaysPerMonth] AS ChildMaxDays, Children.[MinDaysPerMonth] AS ChildMinDays,       
       Children.[MinStartTime] AS ChildMinTime, Children.[MaxStopTime] AS ChildMaxTime,       
       Children.[State] AS ChildState, Children.[ActiveState] AS ChildActiveState,
       Children.[Photo] AS ChildPhoto, Children.ChildLinkId AS ChildLink,
       Children.[Remarks] AS ChildRemarks, 
       Mothers.ID AS MotherId, Mothers.Name AS MotherName, Mothers.[FirstName] AS MotherFirstName,
       Mothers.[Phone] AS MotherPhone, Mothers.[GSM] AS MotherGsm, Mothers.[Email] AS MotherEmail,       
       Mothers.[NationalNumber] AS MotherNationalNumber, Mothers.[Photo] AS MotherPhoto, 
       Mothers.Street AS MotherStreet, Mothers.MainAddress AS MotherMainAddress,       
       mZipCodes.[ID] AS MotherZipcodeId,
       mZipCodes.ZipCode AS MotherZipcode, mZipCodes.[City] AS MotherCity, 
       mCountries.Country AS MotherCountry, mCountries.[CountryCode] AS MotherCountryCode,       
       mCountries.[ID] AS MotherCountryId,
       mEmployers.Id AS MotherEmployerId,
       mEmployers.Name AS MotherEmployerName, mEmployers.[Phone] AS MotherEmployerPhone, 
       mEmployers.[Street] AS MotherEmployerStreet,        
       mempZipcodes.[ID] AS MotherEmployerZipcodeId,
       mempZipcodes.[ZipCode] AS MotherEmployerZipcode, mempZipCodes.[City] AS MotherEmployerCity,
       mempCountries.[Country] AS MotherEmployerCountry, mempCountries.[ID] AS MotherEmployerCountryId, 
       mempCountries.[CountryCode] AS MotherEmployerCountryCode,
       
       Fathers.ID AS FatherId, Fathers.Name AS FatherName, Fathers.[FirstName] AS FatherFirstName,       
       Fathers.[Phone] AS FatherPhone, Fathers.[GSM] AS FatherGsm, Fathers.[Email] AS FatherEmail,       
       Fathers.[NationalNumber] AS FatherNationalNumber, Fathers.[Photo] AS FatherPhoto,
       Fathers.Street AS FatherStreet, Fathers.MainAddress AS FatherMainAddress,
       fZipCodes.[City] AS FatherCity, fZipCodes.ZipCode AS FatherZipcode,        
       fZipCodes.[ID] AS FatherZipcodeId, fCountries.[CountryCode] AS FatherCountryCode,
       fCountries.Country AS FatherCountry, fCountries.[ID] AS FatherCountryId, 
       fEmployers.Id AS FatherEmployerId,
       fEmployers.Name AS FatherEmployerName, fEmployers.[Phone] AS FatherEmployerPhone,
       fEmployers.[Street] AS FatherEmployerStreet,
       fempZipcodes.[ZipCode] AS FatherEmployerZipcode, fempZipcodes.[ID] AS FatherEmployerZipcodeId,
       fempZipcodes.[City] AS FatherEmployerCity, fempCountries.[CountryCode] AS FatherEmployerCountryCode,
       fempCountries.[Country] AS FatherEmployerCountry, fempCountries.[ID] AS FatherEmployerCountryId,       
       Schedule.ID AS ScheduleId, Schedule.[ScheduleType] AS ScheduleType, 
       Schedule.[Week1MoFrom] AS Week1MoFrom, Schedule.[Week1MoTo] AS Week1MoTo, 
       Schedule.[Week1TuFrom] AS Week1TuFrom, Schedule.[Week1TuTo] AS Week1TuTo, 
       Schedule.[Week1WeFrom] AS Week1WeFrom, Schedule.[Week1WeTo] AS Week1WeTo, 
       Schedule.[Week1ThFrom] AS Week1ThFrom, Schedule.[Week1ThTo] AS Week1ThTo, 
       Schedule.[Week1FrFrom] AS Week1FrFrom, Schedule.[Week1FrTo] AS Week1FrTo,         
       Schedule.[Week2MoFrom] AS Week2MoFrom, Schedule.[Week2MoTo] AS Week2MoTo, 
       Schedule.[Week2TuFrom] AS Week2TuFrom, Schedule.[Week2TuTo] AS Week2TuTo, 
       Schedule.[Week2WeFrom] AS Week2WeFrom, Schedule.[Week2WeTo] AS Week2WeTo, 
       Schedule.[Week2ThFrom] AS Week2ThFrom, Schedule.[Week2ThTo] AS Week2ThTo, 
       Schedule.[Week2FrFrom] AS Week2FrFrom, Schedule.[Week2FrTo] AS Week2FrTo,  
       Schedule.[Week3MoFrom] AS Week3MoFrom, Schedule.[Week3MoTo] AS Week3MoTo, 
       Schedule.[Week3TuFrom] AS Week3TuFrom, Schedule.[Week3TuTo] AS Week3TuTo, 
       Schedule.[Week3WeFrom] AS Week3WeFrom, Schedule.[Week3WeTo] AS Week3WeTo, 
       Schedule.[Week3ThFrom] AS Week3ThFrom, Schedule.[Week3ThTo] AS Week3ThTo, 
       Schedule.[Week3FrFrom] AS Week3FrFrom, Schedule.[Week3FrTo] AS Week3FrTo,       
       Schedule.[Week4MoFrom] AS Week4MoFrom, Schedule.[Week4MoTo] AS Week4MoTo, 
       Schedule.[Week4TuFrom] AS Week4TuFrom, Schedule.[Week4TuTo] AS Week4TuTo, 
       Schedule.[Week4WeFrom] AS Week4WeFrom, Schedule.[Week4WeTo] AS Week4WeTo, 
       Schedule.[Week4ThFrom] AS Week4ThFrom, Schedule.[Week4ThTo] AS Week4ThTo, 
       Schedule.[Week4FrFrom] AS Week4FrFrom, Schedule.[Week4FrTo] AS Week4FrTo,       
       ChildrenDiaries.[ArrivalDateTime] AS ArrivalDateTime,       
       ChildrenDiaries.[DepartureDateTime] AS DepartureDateTime,       
TrustPersons.[ID] AS TrustPersonId,
       Children.[Id] AS TrustPersonChildId,
       TrustPersons.[Name] AS TrustPersonName,
       TrustPersons.[FirstName] AS TrustPersonFirstName,       
       TrustPersons.[NationalNumber] AS TrustPersonNationalNumber, 
       TrustPersons.[Email] AS TrustPersonEmail,       
       TrustPersons.[GSM] AS TrustPersonGsm,       
       TrustPersons.[Phone] AS TrustPersonPhone,       
       TrustPersons.[Photo] AS TrustPersonPhoto,       
       TrustPersons.[Street] AS TrustPersonStreet,
       TrustPersons.[UseChildLink] AS TrustPersonUseChildLink,
       ZipCodes.[ZipCode] AS TrustPersonZipcode,
       ZipCodes.[City] AS TrustPersonCity,
       ZipCodes.[ID] AS TrustPersonZipcodeId,
       Countries.[Country] AS TrustPersonCountry,
       Countries.[CountryCode] AS TrustPersonCountryCode,
       Countries.[Id] AS TrustPersonCountryId  

FROM Children INNER JOIN Mothers ON Children.MotherId = Mothers.ID 
     LEFT JOIN Schedule ON Children.ID = Schedule.ChildId
     LEFT JOIN Fathers ON Children.FatherId = Fathers.ID 
     LEFT JOIN ZipCodes mZipCodes ON Mothers.ZipcodeId = mZipCodes.ID 
     LEFT JOIN ZipCodes fZipCodes ON Fathers.ZipcodeId = fZipCodes.ID 
     LEFT JOIN Countries mCountries ON mZipCodes.CountryId = mCountries.ID 
     LEFT JOIN Countries fCountries ON fZipCodes.CountryId = fCountries.ID     
     LEFT JOIN ParentsEmployers mEmployers ON mEmployers.[ID] = Mothers.EmployerId    
     LEFT JOIN ZipCodes mempZipcodes ON mempZipcodes.[ID] = mEmployers.ZipcodeId      
     LEFT JOIN Countries mempCountries ON mempCountries.[ID] = mempZipcodes.CountryId
     LEFT JOIN ParentsEmployers fEmployers ON fEmployers.[ID] = Fathers.EmployerId     
     LEFT JOIN ZipCodes fempZipcodes ON fempZipcodes.[ID]=fEmployers.ZipcodeId     
     LEFT JOIN Countries fempCountries ON fempCountries.[ID] = fempZipcodes.CountryId     
     LEFT OUTER JOIN ChildrenDiaries on Children.ID = ChildrenDiaries.ChildId AND ChildrenDiaries.[ArrivalDateTime] between @date1 and @date2     
  INNER JOIN TrustPersons ON (TrustPersons.[ChildId]=Children.Id OR TrustPersons.ChildId=Children.ChildLinkId)   
     LEFT JOIN ZipCodes ON TrustPersons.[ZipcodeId] = ZipCodes.Id
     LEFT JOIN Countries ON Countries.[ID]=ZipCodes.CountryId ";
    }

    private InvoiceMonthReportData SetupInvoiceMonthData( MonthInvoiceData data ) {
      InvoiceMonthReportData reportData = new InvoiceMonthReportData( );
      foreach ( MonthInvoiceData.MonthInvoiceRow row in data.MonthInvoice ) {
        InvoiceMonthReportData.InvoiceMonthRow reportRow = reportData.InvoiceMonth.NewInvoiceMonthRow( );
        reportRow.ChildId = row.ChildId;
        reportRow.ChildName = row.ChildName;
        reportRow.ChildFirstName = row.IsChildFirstNameNull( ) ? string.Empty : row.ChildFirstName;
        reportRow.ChildFullName = ( ( row.IsChildFirstNameNull( ) ? string.Empty : row.ChildFirstName ) + " " + row.ChildName ).Trim( );
        reportRow.ChildDateIn = row.ChildDateIn;
        if ( !row.IsChildDateOutNull( ) ) {
          reportRow.ChildDateOut = row.ChildDateOut;
        }
        reportRow.BasicPrice = row.BasicPrice;
        reportRow.ChildMinTime = row.ChildMinTime;
        reportRow.ChildMaxTime = row.ChildMaxTime;
        if ( !row.IsArrivalDateTimeNull( ) ) {
          reportRow.ChildArrivalDateTime = row.ArrivalDateTime;
        } if ( !row.IsDepartureDateTimeNull( ) ) {
          reportRow.ChildDepartureDateTime = row.DepartureDateTime;
        }
        if ( row.MotherMainAddress ) {
          reportRow.ParentFirstName = row.IsMotherFirstNameNull( ) ? string.Empty : row.MotherFirstName;
          reportRow.ParentName = row.MotherName;
          reportRow.ParentFullName = ( ( row.IsMotherFirstNameNull( ) ? string.Empty : row.MotherFirstName ) + " " + row.MotherName ).Trim( );
          reportRow.ParentStreet = row.MotherStreet;
          reportRow.ParentZipcodeCity = ( ( row.IsMotherCountryCodeNull( ) ? string.Empty : row.MotherCountryCode ) + " "
                                      + ( row.IsMotherZipcodeNull( ) ? string.Empty : row.MotherZipcode ) + " "
                                      + ( row.IsMotherCityNull( ) ? string.Empty : row.MotherCity ) ).Trim( );
        } else {
          reportRow.ParentFirstName = row.IsFatherFirstNameNull( ) ? string.Empty : row.FatherFirstName;
          reportRow.ParentName = row.FatherName;
          reportRow.ParentFullName = ( ( row.IsFatherFirstNameNull( ) ? string.Empty : row.FatherFirstName ) + " " + row.FatherName ).Trim( );
          reportRow.ParentStreet = row.FatherStreet;
          reportRow.ParentZipcodeCity = ( ( row.IsFatherCountryCodeNull( ) ? string.Empty : row.FatherCountryCode ) + " "
                                      + ( row.IsFatherZipcodeNull( ) ? string.Empty : row.FatherZipcode ) + " "
                                      + ( row.IsFatherCityNull( ) ? string.Empty : row.FatherCity ) ).Trim( );
        }
        reportData.InvoiceMonth.AddInvoiceMonthRow( reportRow );
      }
      return reportData;
    }

    #endregion
  }
}
